<?php

/**
 * prices actions.
 *
 * @package    retailbrain
 * @subpackage prices
 * @author     Your name here
 * @version    SVN: $Id: actions.class.php 23810 2009-11-12 11:07:44Z Kris.Wallsmith $
 */
class pricesActions extends sfActions {

    /**
     * Termina la session por inactividad
     */
    public function preExecute() {
        if (!$this->getUser()->isAuthenticated()) {
            $this->getUser()->setAuthenticated(false);
            $this->getUser()->getAttributeHolder()->clear();
            $this->redirect('/login');
        }
    }

    /**
     * Executes index action
     *
     * @param sfRequest $request A request object
     */
    public function executeIndex(sfWebRequest $request) {

        $this->title = "Precios";
        $this->breadcrumbs = array(array("Precios", "prices/index"));

        $user = $this->getUser()->getAttribute('id_users');
        $this->caps = $this->getUser()->getAttribute('caps');

        $id = $request->getParameter('id');

        $q = Doctrine_Query::create()
                ->from('UserTable ut')
                ->leftJoin('ut.UserTableOrder uto')
                ->where('ut.table_user = ?', array('Precios'))
                ->addWhere('uto.id_users = ?', array($this->getUser()->getAttribute('id_users')))
                ->orderBy('uto.orden');

        $this->userTableOrder = $q->fetchArray();

        $this->userOrderCount = count($q->fetchArray());


        if (!$this->userOrderCount) {
            $q = Doctrine_Query::create()
                    ->from('UserTable ut')
                    ->leftJoin('ut.UserTableOrder uto')
                    ->where('ut.table_user = ?', array('Precios'))
                    ->orderBy('ut.id_tablecolumn');

            $this->userTableOrder = $q->fetchArray();
        }


        /** Prices * */
        $q = Doctrine_Query::create()
                ->from('StorePrices sp')
                ->innerJoin('sp.Products p')
                ->innerJoin('sp.CategoryProduct cp')
                ->innerJoin('sp.Brands b')
                ->where('sp.id_store = ?', array($id))
                ->orderBy('sp.id_price');

        $this->prices = $q->fetchArray();
    }

    /**
     * Executes index action
     *
     * @param sfRequest $request A request object
     */
    public function executeCreateOrder(sfWebRequest $request) {


        $order = $request->getParameter('order');
        $visible = $request->getParameter('visible');
        $table_column = $request->getParameter('tableColumn');

        $user = $this->getUser()->getAttribute('id_users');

        myFunctionsTables::setOrderTable($table_column, $order, $visible, $user, "Precios");

        $this->redirect('/prices/index');
    }

    /**
     * Executes index action
     *
     * @param sfRequest $request A request object
     */
    public function executeClearOrder(sfWebRequest $request) {

        $this->setLayout(false);
        $user = $this->getUser()->getAttribute('id_users');

        $this->forward404Unless($order = Doctrine::getTable('UserTableOrder')->findBySql("id_users = ? and table_user = ?", array($user, "Precios")));
        try {
            $order->delete();
        } catch (Exception $e) {
            //$this->redirect('cliente/index');
        }

        $this->redirect('/prices/index');
    }

    public function executeExcelPrices(sfWebRequest $request) {

        $objPHPExcel = new PHPExcel();

        // Set properties
        $objPHPExcel->getProperties()->setCreator("RetailBrain");
        $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
        $objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
        $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToWidth(1);
        $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToHeight(0);

        // Add some data
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 1, 'PRODUCTO');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 1, 'PRECIO');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, 1, 'CÓDIGO PROVEEDOR');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, 1, 'CATEGORÍA');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, 1, 'MARCA');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, 1, 'ÚNIDADES');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, 1, 'PRECIO SUGERIDO');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, 1, 'PRECIO HOY');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8, 1, 'PRECIO SEMANAL');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(9, 1, 'DIFERENCIA');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10, 1, 'PRECIO SEMANA 1');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(11, 1, 'PROMEDIO SEMANAL');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(12, 1, 'MÁXIMO');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(13, 1, 'MÍNIMO');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(14, 1, 'DESVIACIÓN');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(15, 1, 'COMPETIDORES');


        $id = $request->getParameter('id');

        /** Prices * */
        $q = Doctrine_Query::create()
                ->from('StorePrices sp')
                ->innerJoin('sp.Products p')
                ->innerJoin('sp.CategoryProduct cp')
                ->innerJoin('sp.Brands b')
                ->where('sp.id_store = ?', array($id))
                ->orderBy('sp.id_price');

        $prices = $q->fetchArray();


        $i = 2;
        foreach ($prices as $value) {

            $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $value['Products']['name']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $value['observed_price']);
            $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $value['code_supplier']);
            $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $value['CategoryProduct']['name']);
            $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, $value['Brands']['name']);
            $objPHPExcel->getActiveSheet()->setCellValue('F' . $i, $value['units']);
            $objPHPExcel->getActiveSheet()->setCellValue('G' . $i, $value['price_suggested']);
            $objPHPExcel->getActiveSheet()->setCellValue('H' . $i, $value['price_today']);
            $objPHPExcel->getActiveSheet()->setCellValue('I' . $i, $value['price_week']);
            $objPHPExcel->getActiveSheet()->setCellValue('J' . $i, $value['difference']);
            $objPHPExcel->getActiveSheet()->setCellValue('K' . $i, $value['price_week_last']);
            $objPHPExcel->getActiveSheet()->setCellValue('L' . $i, $value['average_weekly']);
            $objPHPExcel->getActiveSheet()->setCellValue('M' . $i, $value['maximal']);
            $objPHPExcel->getActiveSheet()->setCellValue('N' . $i, $value['minimal']);
            $objPHPExcel->getActiveSheet()->setCellValue('O' . $i, $value['desviation']);
            $objPHPExcel->getActiveSheet()->setCellValue('P' . $i, $value['competitors']);

            $i++;
        }

        $objPHPExcel->getActiveSheet()->setTitle('Precios');

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="precios.xlsx"');
        header('Cache-Control: max-age=0');

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save('php://output');
        exit;

        return sfview::NONE;
    }

    /**
     * Executes updateUser action
     * 
     * @param sfWebRequest $request
     * @return type 
     */
    public function executeUpdatePrice(sfWebRequest $request) {

        $id = $request->getParameter('pk');
        $value = $request->getParameter('value');
        $store = $request->getParameter('store');

        $this->setPriceObserved($id, $value);
        $this->setPriceCheck($store);

        $priceToday = $this->getPriceToday($id);
        $priceWeek = $this->getPriceWeek($id);
        $priceDifference = $this->getPriceDifference($id);
        $priceLastWeek = $this->getPriceLastWeek($id);
        $priceAvgWeek   = $this->getPriceAvgWeek($id);
        $priceMaxMin    = $this->getPriceMaxMin($id);
        $priceDesviation = $this->getPriceDesviation($id);
        

        try {

            $tabla = Doctrine::getTable('StorePrices')->find(array($id));

            $tabla->observed_price = $value;
            $tabla->price_today = $priceToday;
            $tabla->price_week = $priceWeek;
            $tabla->difference = $priceDifference;
            $tabla->price_week_last = $priceLastWeek;
            $tabla->average_weekly = $priceAvgWeek;
            $tabla->maximal         = $priceMaxMin['maximo'];
            $tabla->minimal         = $priceMaxMin['minimo'];
            $tabla->desviation      = $priceDesviation;

            $tabla->save();
        } catch (Exception $e) {
            syslog(LOG_ERR, "Error: " . $e->getMessage());
        }


        $this->getResponse()->setContent(json_encode(array("id" => $id, 
            "priceToday" => (int) $priceToday,
            "PriceWeek" => (int) $priceWeek,
            "PriceDiferrence" => (int) $priceDifference,
            "PriceLastWeek" => (int) $priceLastWeek,
            "PriceAvgWeek" => (int) $priceAvgWeek,
            "PriceMax" => (int) $priceMaxMin['maximo'],
            "PriceMin" => (int) $priceMaxMin['minimo'],
            "PriceDesviation" => number_format($priceDesviation,2,',','.'),
            )));
        return sfView::NONE;
    }

    public function setPriceObserved($id, $value) {

        $user = $this->getUser()->getAttribute('id_users');

        try {
            $tabla = Doctrine::getTable('StorePricesDetails');
            $tabla = new StorePricesDetails();

            $tabla->id_price = $id;
            $tabla->id_users = $user;
            $tabla->observed_price = $value;
            $tabla->price_date = date('Y-m-d H:m:s');

            $tabla->save();
        } catch (Exception $e) {
            syslog(LOG_ERR, "Error: " . $e->getMessage());
        }
    }

    
    public function setPriceCheck($idstore) {

        $user = $this->getUser()->getAttribute('id_users');

        try {
            $tabla = Doctrine::getTable('StoreCheckprice');
            $tabla = new StoreCheckprice();

            $tabla->id_store = $idstore;
            $tabla->id_users = $user;
            $tabla->checkprice_date = date('Y-m-d H:m:s');

            $tabla->save();
        } catch (Exception $e) {
            syslog(LOG_ERR, "Error: " . $e->getMessage());
        }
    }
    
    public function getPriceToday($id) {

        $moda = 0;
        $frec = 0;

        $q = Doctrine_Query::create()
                ->select('spd.observed_price as price')
                ->from('StorePricesDetails spd')
                ->where('spd.id_price = ?', array($id))
                ->addWhere('spd.price_date >= ? ', array(date('Y-m-d') . " 00:00:00"))
                ->addWhere('spd.price_date <= ? ', array(date('Y-m-d') . " 23:59:59"));

        $prices = $q->fetchArray();
        
        foreach ($prices as $value) {
            $numeros[] = $value['price'];
        }

        $moda = $this->getModa($numeros, 0, count($numeros) - 1);

        return $moda;
    }

    public function getModa($numeros, $prim, $ult) {
        $i = 0;
        $frec = 0;
        $maxfrec = 0;
        $moda;

        if ($prim == $ult)
            return $numeros[$prim];
        $moda = $numeros[$prim];
        $maxfrec = $this->getFrecuencia($numeros, $numeros[$prim], $prim, $ult);

        for ($i = $prim + 1; $i <= $ult; $i++) {
            $frec = $this->getFrecuencia($numeros, $numeros[$i], $i, $ult);

            if ($frec > $maxfrec) {
                $maxfrec = $frec;
                $moda = $numeros[$i];
            }
        }

        return $moda;
    }

    public function getFrecuencia($numeros, $p, $prim, $ult) {
        $i = 0;
        $suma = 0;

        if ($prim > $ult) {
            return 0;
        }

        $suma = 0;

        for ($i = $prim; $i <= $ult; $i++) {
            if ($numeros[$i] == $p) {
                $suma++;
            }
        }

        return $suma;
    }
    
    
    public function getPriceWeek($id) {

        $finicio = date('Y-m-d', strtotime( date('Y-m-d') . ' -3 day'));
        $ftermino = date('Y-m-d', strtotime( date('Y-m-d') . ' +3 day'));

        $q = Doctrine_Query::create()
                ->select('avg(spd.observed_price) as price')
                ->from('StorePricesDetails spd')
                ->where('spd.id_price = ?', array($id))
                ->addWhere('spd.price_date >= ? ', array($finicio . " 00:00:00"))
                ->addWhere('spd.price_date <= ? ', array($ftermino . " 23:59:59"));

        $prices = $q->fetchArray();
        

        return $prices[0]['price'];
    }
    
    
    public function getPriceDifference($id) {

        $finicio = date('Y-m-d', strtotime( date('Y-m-d') . ' -3 day'));
        $ftermino = date('Y-m-d', strtotime( date('Y-m-d') . ' +3 day'));

        $q = Doctrine_Query::create()
                ->select('avg(spd.observed_price) as price')
                ->from('StorePricesDetails spd')
                ->where('spd.id_price = ?', array($id))
                ->addWhere('spd.price_date >= ? ', array($finicio . " 00:00:00"))
                ->addWhere('spd.price_date <= ? ', array($ftermino . " 23:59:59"));

        $pricesWeek = $q->fetchArray();
        

        $q = Doctrine_Query::create()
                ->select('sp.price_suggested as price')
                ->from('StorePrices sp')
                ->where('sp.id_price = ?', array($id));

        $pricesSuggested = $q->fetchArray();
        
        
        return (100 - (($pricesWeek[0]['price']*100)/$pricesSuggested[0]['price']));
    }

    
    public function getPriceLastWeek($id) {
     
        $finicio = date('Y-m-d', strtotime('last week'));
        $ftermino = date('Y-m-d', strtotime('this week -1 day'));
        
         $q = Doctrine_Query::create()
                ->select('avg(spd.observed_price) as price')
                ->from('StorePricesDetails spd')
                ->where('spd.id_price = ?', array($id))
                ->addWhere('spd.price_date >= ? ', array($finicio . " 00:00:00"))
                ->addWhere('spd.price_date <= ? ', array($ftermino . " 23:59:59"));

        $pricesLastWeek = $q->fetchArray();
        
        
        return $pricesLastWeek[0]['price'];
    }
    
    
    public function getPriceAvgWeek($id) {

        $finicio = date('Y-m-d', strtotime('this week' ));
        $ftermino = date('Y-m-d', strtotime('this week +6 day'));

        $q = Doctrine_Query::create()
                ->select('avg(spd.observed_price) as price')
                ->from('StorePricesDetails spd')
                ->where('spd.id_price = ?', array($id))
                ->addWhere('spd.price_date >= ? ', array($finicio . " 00:00:00"))
                ->addWhere('spd.price_date <= ? ', array($ftermino . " 23:59:59"));

        $prices = $q->fetchArray();
        

        return $prices[0]['price'];
    }
    
    
    public function getPriceMaxMin($id) {
        
        $finicio = date('Y-m-d', strtotime( date('Y-m-d') . ' -3 day'));
        $ftermino = date('Y-m-d', strtotime( date('Y-m-d') . ' +3 day'));

        $q = Doctrine_Query::create()
                ->select('MAX(spd.observed_price) as maximo, MIN(spd.observed_price) as minimo ')
                ->from('StorePricesDetails spd')
                ->where('spd.id_price = ?', array($id))
                ->addWhere('spd.price_date >= ? ', array($finicio . " 00:00:00"))
                ->addWhere('spd.price_date <= ? ', array($ftermino . " 23:59:59"));

        $pricesMaxMin = $q->fetchArray();
        
        return array("maximo" => $pricesMaxMin[0]['maximo'],"minimo" => $pricesMaxMin[0]['minimo']);
    }
    
    
    
    public function getPriceDesviation($id) {
        
        $finicio = date('Y-m-d', strtotime( date('Y-m-d') . ' -3 day'));
        $ftermino = date('Y-m-d', strtotime( date('Y-m-d') . ' +3 day'));

        $q = Doctrine_Query::create()
                ->select('spd.observed_price')
                ->from('StorePricesDetails spd')
                ->where('spd.id_price = ?', array($id))
                ->addWhere('spd.price_date >= ? ', array($finicio . " 00:00:00"))
                ->addWhere('spd.price_date <= ? ', array($ftermino . " 23:59:59"));

        $pricesDesviation = $q->fetchArray();
        
        $cantPrices = count($pricesDesviation);
        $sumPrices = 0;
        foreach ($pricesDesviation as $value) {
            $sumPrices+=$value['observed_price'];
            
        }
        
        $media = $sumPrices/$cantPrices;
        $sumPricesVarianza = 0;
        foreach ($pricesDesviation as $value) {
            $sumPricesVarianza+= pow(($value['observed_price']-$media),2);
            
        }
        
        $varianza = $sumPricesVarianza/$cantPrices;
        
        return $desviation = sqrt($varianza);
    }
}
